package pl.koszolko.documentclustering.dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pl.koszolko.documentclustering.dao.Author;
import pl.koszolko.documentclustering.dao.AuthorDao;
import pl.koszolko.documentclustering.dao.Authorship;
import pl.koszolko.documentclustering.dao.AuthorshipDao;
import pl.koszolko.documentclustering.dao.Document;
import pl.koszolko.documentclustering.dao.DocumentDao;
import pl.koszolko.documentclustering.dao.utils.DataBaseConnectionManager;
import pl.koszolko.documentclustering.dao.utils.DataBaseUtils;

public class JdbcDocumentDao implements DocumentDao {
	private static final String INSERT = "insert into PUBLIKACJE VALUES(?,?,?,?)";
	private static final String UPDATE = "update PUBLIKACJE set TYTUL = ?, ABSTRAKT = ? where ID_PUBLIKACJI = ?";
	private static final String DELETE = "delete from PUBLIKACJE where ID_PUBLIKACJI = ?";
	private static final String FIND_ALL = "select ID_PUBLIKACJI, GRUPA, TYTUL, ABSTRAKT from PUBLIKACJE";
	@SuppressWarnings("unused")
	private static final String FIND_DOCUMENT = "select ID_PUBLIKACJI, from PUBLIKACJE d, CYTOWANIA c";
	private static final String SEQ = "select nextval('publikacje_seq')";
	
	private DataBaseConnectionManager connectionManager;
	
	public JdbcDocumentDao() {
	}
	
	public JdbcDocumentDao(DataBaseConnectionManager connectionManager) {
		this.connectionManager = connectionManager;
	}
	
	@Override
	public void insert(Document document) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			List<Long> authorsIds = saveAuthors(document);
			
			//sprawdzenie czy istnieje juz taki dokument w bazie
			Long id = null;
			if((id = checkIfExistInDB(document, authorsIds)) == null) {
				id = DataBaseUtils.getNextValFromSeq(conn, SEQ);
				document.setId(id);
				
				PreparedStatement ps = conn.prepareStatement(INSERT);
				ps.setLong(1, document.getId());
				ps.setString(2, document.getDocumentClass());
				ps.setString(3, document.getTitle());
				ps.setString(4, document.getAbstractText());
				ps.executeUpdate();
				ps.close();
				
				//zapisanie autorstwa
				saveAuthorships(id, authorsIds);
			} else {
				document.setId(id);
			}

		} catch (SQLException e) {
			System.out.println("Problem z zapisem artykulu: " + document.getTitle());
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}

	private void saveAuthorships(Long id, List<Long> authorsIds) {
		AuthorshipDao dao = new JdbcAuthorshipDao(connectionManager);
		
		for(Long authorId : authorsIds ) {
			Authorship authorship = new Authorship();
			authorship.setAuthorId(authorId);
			authorship.setDocumentId(id);
			dao.insert(authorship);
		}
	}

	private Long checkIfExistInDB(Document document, List<Long> authorsIds) {
		String findId = "select p.ID_PUBLIKACJI from PUBLIKACJE p where p.tytul = ?";
		String findAuthorId = "select id_publikacji from autorzy_publikacji where id_publikacji = ? and id_autora = ?";
		Long docId = null;
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();		
			PreparedStatement ps = conn.prepareStatement(findId);
			ps.setString(1, document.getTitle());
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()) {
				docId = rs.getLong(1);
				System.out.println("Pobrano dokuemnt o id " + docId);
			}
			rs.close();
			ps.close();
			
			if(docId != null) {
				for(Long authorId : authorsIds) {
					System.out.println("Sprawdzanie czy dokument o id " + docId + " na autora o id " + authorId);
					ps = conn.prepareStatement(findAuthorId);
					ps.setLong(1, docId);
					ps.setLong(2, authorId);
					rs = ps.executeQuery();
					
					if(!rs.next()) {
						System.out.println("Nie ma");
						docId = null;
						break;
					}
					rs.close();
					ps.close();
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		
		return docId;
	}
	
	private List<Long> saveAuthors(Document document) {
		AuthorDao dao = new JdbcAuthorDao(connectionManager);
		List<Long> ids = new ArrayList<Long>();
		
		//zapis autorow jezeli sa
		for(Author author: document.getAuthors()) {
			ids.add(saveAuthor(author, dao));
		}
		
		return ids;
	}

	private Long saveAuthor(Author author, AuthorDao dao) {
		dao.insert(author);
		return author.getId();
	}

	@Override
	public void update(Document document) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			PreparedStatement ps = conn.prepareStatement(UPDATE);
			ps.setString(1, document.getTitle());
			ps.setString(2, document.getAbstractText());
			ps.setLong(3, document.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}

	@Override
	public void delete(Long documentId) {
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();
			
			PreparedStatement ps = conn.prepareStatement(DELETE);
			ps.setLong(1, documentId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
	}

	@Override
	public List<Document> findAll() {
		List<Document> documents = new ArrayList<Document>();
		Connection conn = null;
		try {
			conn = connectionManager.getConnection();		
			PreparedStatement ps = conn.prepareStatement(FIND_ALL);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				Document document = new Document();
				document.setId(rs.getLong(1));
				document.setDocumentClass(rs.getString(2));
				document.setTitle(rs.getString(3));
				document.setAbstractText(rs.getString(4));
				documents.add(document);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e2) {
			e2.printStackTrace();
		} finally {
			if(conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		
		return documents;
	}

	public DataBaseConnectionManager getConnectionManager() {
		return connectionManager;
	}

	public void setConnectionManager(DataBaseConnectionManager connectionManager) {
		this.connectionManager = connectionManager;
	}

}
